//
//  HZMSqlTool.m
//  糖糖大魔王
//
//  Created by 韩震明 on 15/11/8.
//  Copyright © 2015年 韩震明. All rights reserved.
//

#import "HZMSqlTool.h"
#import "FMDatabaseAdditions.h"

static HZMSqlTool *mySqlTool = nil;

@implementation HZMSqlTool

@synthesize db = db;
+ (HZMSqlTool *)sharedInstance
{
    static dispatch_once_t onceDispatch;
    dispatch_once(&onceDispatch,^{
        
        mySqlTool = [[HZMSqlTool alloc]init];
        
    });
    return mySqlTool;
}
//获得存放数据库文件的沙盒地址
- (NSString *)databaseFilePath
{
    NSArray *filePath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentPath = [filePath objectAtIndex:0];
    NSLog(@"%@",filePath);
    NSString *dbFilePath = [documentPath stringByAppendingPathComponent:@"db.sqlite"];
    return dbFilePath;
    
}
//创建数据库的操作
- (void)creatDatabase
{
    db = [[FMDatabase databaseWithPath:[self databaseFilePath]]retain];
    
}

- (void)creatTable:(NSString*)tableName
{
    //先判断数据库是否存在，如果不存在，创建数据库
    if (!db) {
        [self creatDatabase];
    }
    //判断数据库是否已经打开，如果没有打开，提示失败
    if (![db open]) {
        NSLog(@"数据库打开失败");
        return;
    }
    //为数据库设置缓存，提高查询效率
    [db setShouldCacheStatements:YES];
    
    //判断数据库中是否已经存在这个表，如果不存在则创建该表
    if ([db open]) {
        if (![db tableExists:tableName]) {
            NSString *sql = [NSString stringWithFormat:@"CREATE TABLE '%@' ('islike' bool)", tableName];
            BOOL res = [db executeUpdate:sql];
            if (!res) {
                NSLog(@"error when creating db table");
            } else {
                NSLog(@"succ to creating db table");
            }
            [db close];
            
        }else{
            NSLog(@"table exist!!!!!");
        }
    } else {
        NSLog(@"error when open db");
    }
}

- (void)insertData:(NSDictionary*)dic intoTable:(NSString *)tableName
{
    //判断是否有数据库
    if (!db) {
        [self creatDatabase];
    }
    if (![db open]) {
        NSLog(@"db open error");
        return;
    }
    //判断数据库中是否已经存在这个表，如果不存在则创建该表
    if (![db tableExists:tableName]) {
        [self creatTable:tableName];
    }
    // 查询表中是否有该条数据，有则修改，无则插入
    NSString *seachStr = [NSString stringWithFormat:@"select * from %@ where name = ?",tableName];
    
    FMResultSet *rs = [db executeQuery:seachStr,[dic objectForKey:@"name"]];
    if ([rs next]) {
        NSString *updateStr = [NSString stringWithFormat:@"update %@ set age = ? where name = ?",tableName];
        BOOL res = [db executeUpdate:updateStr,[dic objectForKey:@"age"],[dic objectForKey:@"name"]];
        if (res) {
            NSLog(@"data update success!");
        }else{
            NSLog(@"data update fail!");
        }
    }else{
        NSString *insertStr = [NSString stringWithFormat:@"INSERT INTO %@ (name, age) VALUES (?,?)",tableName];
        BOOL res = [db executeUpdate:insertStr,[dic objectForKey:@"name"],[dic objectForKey:@"age"]];
        if (res) {
            NSLog(@"data insert success!");
        }else{
            NSLog(@"data insert fail!");
        }
        
    }
    [db close];
    
}
- (void)deleteData:(NSDictionary *)dic fromTable:(NSString *)tableName
{
    //判断是否有数据库
    if (!db) {
        [self creatDatabase];
    }
    if (![db open]) {
        NSLog(@"db open error");
        return;
    }
    //判断数据库中是否已经存在这个表，如果不存在则创建该表
    if (![db tableExists:tableName]) {
        [self creatTable:tableName];
    }
    //判断数据库中是否有这条数据，有则删除
    NSString *sqlStr = [NSString stringWithFormat:@"select * from %@ where name = ?",tableName];
    FMResultSet *rs = [ db executeQuery:sqlStr,[dic objectForKey:@"name"]];
    if ([rs next]) {
        NSString *deleteStr = [NSString stringWithFormat:@"delete from %@ where name = ?",tableName];
        BOOL res = [db executeUpdate:deleteStr,[dic objectForKey:@"name"]];
        if (res ) {
            NSLog(@"delete data sucess!!!!");
        }else{
            NSLog(@"delete data failure!");
        }
    }
    [db close];
}

- (NSDictionary *)queryDataFromTable:(NSString *)tableName
{
    //判断是否有数据库
    if (!db) {
        [self creatDatabase];
    }
    if (![db open]) {
        NSLog(@"db open error");
        return nil;
    }
    //判断数据库中是否已经存在这个表，如果不存在则创建该表
    if (![db tableExists:tableName]) {
        [self creatTable:tableName];
    }
    if ([db open]) {
        NSString * sql = [NSString stringWithFormat:@"select * from %@",tableName];
        FMResultSet * rs = [db executeQuery:sql];
        while ([rs next]) {
            NSString * name = [rs stringForColumn:@"name"];
            int  age = [rs intForColumn:@"age"];
            NSLog(@"name = %@,age = %d",name,age);
            NSDictionary *divc = [[NSDictionary alloc]initWithObjectsAndKeys:name,@"name",[NSNumber numberWithInt:age],@"age", nil];
            return divc;
        }
        [db close];
    }
    return nil;
}


@end
